package cn.lxycx.dataterrace.util;
import java.io.FileOutputStream;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import lxycx.util.format.StringUitl;

import org.apache.poi.xwpf.usermodel.ParagraphAlignment;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.apache.poi.xwpf.usermodel.XWPFTable;
import org.apache.poi.xwpf.usermodel.XWPFTableCell;
import org.apache.poi.xwpf.usermodel.XWPFTableCell.XWPFVertAlign;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTbl;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTblWidth;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTc;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.CTTcPr;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STJc;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STTblWidth;
import org.openxmlformats.schemas.wordprocessingml.x2006.main.STVerticalJc;

import com.jfinal.plugin.activerecord.ActiveRecordPlugin;
import com.jfinal.plugin.activerecord.CaseInsensitiveContainerFactory;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.dialect.OracleDialect;
import com.jfinal.plugin.druid.DruidPlugin;


/**
 * 自动生成数据库结构文档
 * @author 邢超
 * 时间：2017年10月13日
 */
public class QuickDD {
	private static final String DRIVER = "oracle.jdbc.driver.OracleDriver";
	private static final String JDBCURL = "jdbc:oracle:thin:@101.37.149.47:11521:ORCL";
	private static final String USERNAME = "hlkj_xc";//"js_gongan";
	private static final String PASSWORD = "HuiLong_Xc";//"HLKJ#jsga2018";
	
	/*static {
		//PropKit.use("config");
				DruidPlugin dp = new DruidPlugin(JDBCURL, USERNAME, PASSWORD, DRIVER);
				ActiveRecordPlugin arp = new ActiveRecordPlugin(dp);
				arp.setShowSql(false);//显示sql
				
				arp.setDialect(new OracleDialect());
				// 配置属性名(字段名)大小写不敏感容器工厂
				arp.setContainerFactory(new CaseInsensitiveContainerFactory());
				
				
				dp.setMaxActive(10);
				dp.start();
				arp.start();
	}
	*/
	
	public static void main(String[] args) throws Exception {
		String sql = toLike("SALE");//toIn("JS_BABY_REGISTER");
		System.out.println(sql);
		String name = "中铁宝桥售后服务管理系统数据结构文档";
		toOracleColumn(name,"D:/saveFile/"+ name + ".docx",findParams(sql));
	}
	
	
	
	
	
	public static String toLike(String likeName){
		String sql = "select * from (select  d.table_name tbname,a.column_id columnid,coalesce(t.comments, ' ') tbdesc,"
				+ "a.column_name columnname, a.data_type columntype,a.data_length width,a.data_scale precision,"
				+ "decode(a.nullable,'y','0','1') notnull,a.data_default,coalesce(m.comments, ' ') comments,"
				+ "decode(k.uniqueness,'unique','1','0') uniques,coalesce(k.index_name, ' ') indexname,decode(k.key,'y','1','0') masterkey"
				+ " from user_tab_columns a inner join  user_tables d on a.table_name=d.table_name "
				+ "left join user_tab_comments t on t.table_name=d.table_name "
				+ "left join user_col_comments m on m.column_name=a.column_name and m.table_name=d.table_name "
				+ "left join(select e.index_name,u.table_name,u.column_name,e.uniqueness,decode(p.constraint_name,null,'n','y') key "
				+ "from user_indexes e inner join user_ind_columns u on e.index_name=u.index_name "
				+ "left join ( select constraint_name from user_constraints where constraint_type='p' ) p on e.index_name=p.constraint_name"
				+ ") k on k.table_name=a.table_name and k.column_name=a.column_name"
				+ ") where tbname like '%"+likeName+"%' order by tbname,columnid";
		
		return sql;
	}
	
	
	public static String toIn(String ...tablename){
		String sql = "select * from (select  d.table_name tbname,a.column_id columnid,coalesce(t.comments, ' ') tbdesc,"
				+ "a.column_name columnname, a.data_type columntype,a.data_length width,a.data_scale precision,"
				+ "decode(a.nullable,'y','0','1') notnull,a.data_default,coalesce(m.comments, ' ') comments,"
				+ "decode(k.uniqueness,'unique','1','0') uniques,coalesce(k.index_name, ' ') indexname,decode(k.key,'y','1','0') masterkey"
				+ " from user_tab_columns a inner join  user_tables d on a.table_name=d.table_name "
				+ "left join user_tab_comments t on t.table_name=d.table_name "
				+ "left join user_col_comments m on m.column_name=a.column_name and m.table_name=d.table_name "
				+ "left join(select e.index_name,u.table_name,u.column_name,e.uniqueness,decode(p.constraint_name,null,'n','y') key "
				+ "from user_indexes e inner join user_ind_columns u on e.index_name=u.index_name "
				+ "left join ( select constraint_name from user_constraints where constraint_type='p' ) p on e.index_name=p.constraint_name"
				+ ") k on k.table_name=a.table_name and k.column_name=a.column_name"
				+ ") where tbname in('"+StringUitl.toString("','", tablename)+"') order by tbname,columnid";
		
		return sql;
	}
	
	
	
	
	/**
	 * 查询出表结构封装成map
	 * @param likeName 查询指定名称的表
	 * @return
	 * @author 邢超
	 * 创建时间：2017年8月23日
	 *
	 */
	public static Map<String,ArrayList<Record>> findParams(String sql){	
		
		
		List<Record> rt = Db.find(sql);
		Map<String,ArrayList<Record>> mapp = new HashMap<String, ArrayList<Record>>();
		
		for(Record r:rt){
			String tname = r.get("tbname");
			boolean flag = mapp.containsKey(tname);
			if(flag){
				mapp.get(tname).add(r);
			}else{
				ArrayList<Record> list = new ArrayList<Record>();
				list.add(r);
				mapp.put(tname, list);
			}
		}
		
		
		return mapp;
	}

	/**
	 * 导出表结构到文档
	 * @param title 文档标题
	 * @param path 文档路径
	 * @param mapp 查询出来的表结构 key 表名，value字段的相关属性信息
	 * @return
	 * @throws Exception
	 * @author 邢超
	 * 创建时间：2017年8月23日
	 *
	 */
	public static boolean toOracleColumn(String title,String path,Map<String,ArrayList<Record>> mapp) throws Exception{
		
        FileOutputStream fos = new FileOutputStream(path);  
        XWPFDocument xdoc = new XWPFDocument();
        
        XWPFParagraph xp = xdoc.createParagraph();
		XWPFRun r1 = xp.createRun();
		r1.setText(title);
		r1.setFontFamily("宋体");
		r1.setFontSize(25);
		r1.setTextPosition(10);
		r1.setBold(true);
		xp.setAlignment(ParagraphAlignment.CENTER);
		
		for(String tname:mapp.keySet()){
			List<Record> records = mapp.get(tname);
			String tbdesc = records.get(0).getStr("tbdesc");
			if(tbdesc==null||"".equals(tbdesc.trim())){//表说明不能为空否则直接跳过
				continue;
			}
			xp = xdoc.createParagraph();
			r1 = xp.createRun();
			r1.addBreak();r1.addBreak();
			r1.setText(tname+":"+tbdesc);
			r1.setFontFamily("宋体");
			r1.setFontSize(12);
			r1.setTextPosition(10);
			r1.setBold(true);
			xp.setAlignment(ParagraphAlignment.LEFT);//设置文字位置居左
			
			XWPFTable xTable = xdoc.createTable(records.size()+1,6);
			CTTbl ttbl = xTable.getCTTbl();
			CTTblPr tblPr = ttbl.getTblPr() == null ? ttbl.addNewTblPr() : ttbl  
	                .getTblPr();  
	        CTTblWidth tblWidth = tblPr.isSetTblW() ? tblPr.getTblW() : tblPr  
	                .addNewTblW();  
	        tblWidth.setW(new BigInteger("8600"));  
	        tblWidth.setType(STTblWidth.AUTO);
	  
	        int i = 0;  
	        xTable.getRow(i).setHeight(880);  //调整表格宽度
	        
	        setCellText(xdoc, xTable.getRow(i).getCell(0), "字段名", "CCCCCC",  
	                getCellWidth(0));  
	        setCellText(xdoc, xTable.getRow(i).getCell(1), "类型", "CCCCCC",  
	                getCellWidth(1));  
	        setCellText(xdoc, xTable.getRow(i).getCell(2), "备注", "CCCCCC",  
	        		getCellWidth(2));  
	        setCellText(xdoc, xTable.getRow(i).getCell(3), "默认值", "CCCCCC",  
	                getCellWidth(3));  
	        setCellText(xdoc, xTable.getRow(i).getCell(4), "索引", "CCCCCC",  
	        		getCellWidth(4));  
	        setCellText(xdoc, xTable.getRow(i).getCell(5), "是否为空", "CCCCCC",  
	        		getCellWidth(5));  
	        /*setCellText(xdoc, xTable.getRow(i).getCell(6), "唯一", "CCCCCC",  
	                getCellWidth(6));  
	        setCellText(xdoc, xTable.getRow(i).getCell(7), "主键", "CCCCCC",  
	                getCellWidth(7));  */
	        
	        for(Record re:records){
	        	++i;
	        	setCellText(xdoc, xTable.getRow(i).getCell(0), re.getStr("columnname"), null,getCellWidth(i));
	        	setCellText(xdoc, xTable.getRow(i).getCell(1), re.getStr("columntype")+"("+re.getBigDecimal("width")+")", null,getCellWidth(i)); 
	        	setCellText(xdoc, xTable.getRow(i).getCell(2), re.getStr("comments"), null,getCellWidth(i)); 
	        	setCellText(xdoc, xTable.getRow(i).getCell(3), re.getStr("data_default"), null,getCellWidth(i)); 
	        	setCellText(xdoc, xTable.getRow(i).getCell(4), re.getStr("indexname"), null,getCellWidth(i)); 
	        	setCellText(xdoc, xTable.getRow(i).getCell(5), re.getStr("notnull"), null,getCellWidth(i)); 
/*	        	setCellText(xdoc, xTable.getRow(i).getCell(6), re.getStr("uniques"), null,getCellWidth(i)); 
	        	setCellText(xdoc, xTable.getRow(i).getCell(7), re.getStr("masterkey"), null,getCellWidth(i)); */
	        	
	        }
		}

		xdoc.write(fos);  
		fos.flush();
        fos.close(); 
        
		return false;
		
	}
	
	
	
	 private static void setCellText(XWPFDocument xDocument, XWPFTableCell cell,  
	            String text, String bgcolor, int width) {  
	        CTTc cttc = cell.getCTTc();  
	        CTTcPr cellPr = cttc.addNewTcPr();  
	        cellPr.addNewTcW().setW(BigInteger.valueOf(width));  
	        cell.setColor(bgcolor);  
	        cell.setVerticalAlignment(XWPFVertAlign.CENTER);  
	        CTTcPr ctPr = cttc.addNewTcPr();  
	        ctPr.addNewVAlign().setVal(STVerticalJc.CENTER);  
	        cttc.getPList().get(0).addNewPPr().addNewJc().setVal(STJc.CENTER);  
	        cell.setText(text);  
	  
	 }  
	 
	private static int getCellWidth(int index) {  
        int cwidth = 1000;  
        if (index == 0) {  
            cwidth = 1600;  
        } else if (index == 1) {  
            cwidth = 3000;  
        } else if (index == 2) {  
            cwidth = 1200;  
        } else if (index == 3) {  
            cwidth = 900;  
        } else if (index == 4) {  
            cwidth = 600;  
        } else if (index == 5) {  
            cwidth = 600;  
        } else if (index == 6) {  
            cwidth = 700;  
        }  
        return cwidth;  
    }  
}
